﻿using FirstDemo.Models;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Data.SqlClient;
using System.Data;
using System.Linq;
using System.Threading.Tasks;

// For more information on enabling MVC for empty projects, visit https://go.microsoft.com/fwlink/?LinkID=397860

namespace FirstDemo.Controllers
{
    public class TodoViewController : Controller
    {
        TodoListContext _context = new TodoListContext(); //TodoItem的上下文，用于EF框架
        string source = GlobalString.SQLServerString;    //通过appsettings.json读取数据库连接字符串
        string MySQLSource = GlobalString.MySQLString;

        /// <summary>
        /// 主界面跳转函数
        /// </summary>
        /// <returns>带ViewModel返回值的页面</returns>
        public IActionResult Index()
        {
            var todoItems = from q in _context.TodoItems
                            select q;
            //ViewData["todoItems"] = todoItems;       //通过ViewData进行数据传输，不显式声明要使用的数据类型
            var viewModel = new TodoGenreViewModel();   //强类型数据，让视图充分利用强类型检查
            viewModel.todoItems = todoItems.ToList();
            return View(viewModel);
        }

        /// <summary>
        /// 增加一个代办任务的调用函数，目前使用的是API模式
        /// </summary>
        /// <param name="value">任务名称</param>
        /// <returns>返回Index界面</returns>
        public async Task<IActionResult> Insert([FromBody]string value)
        {
            TodoItem temp = new TodoItem();
            temp.Name = value;
            _context.TodoItems.Add(temp);
            await _context.SaveChangesAsync();
            return RedirectToAction(nameof(Index));
        }

        /// <summary>
        /// 删除一个任务的调用函数
        /// </summary>
        /// <param name="id">需要删除的任务Id</param>
        /// <returns>返回Index界面</returns>
        public async Task<IActionResult> Delete(int id)
        {
            //通过EF进行数据库操作
            //var temp = await _context.TodoItems.FindAsync(id);    
            //_context.TodoItems.Remove(temp);
            //await _context.SaveChangesAsync();

            //通过ADO调用存储过程
            using (SqlConnection conn = new SqlConnection(source))
            {
                await conn.OpenAsync();
                await GenerateCommand(conn, id).ExecuteNonQueryAsync();
                await conn.CloseAsync();
            }

            return RedirectToAction(nameof(Index));
        }
        //用于生成调用存储过程的SqlCommand
        private static SqlCommand GenerateCommand(SqlConnection conn, int id)
        {
            SqlCommand command = new SqlCommand("TodoItemDelete", conn);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.AddWithValue("@Id", id);
            command.UpdatedRowSource = UpdateRowSource.None;
            return command;
        }

        /// <summary>
        /// 更新任务为已完成的调用函数
        /// </summary>
        /// <param name="id">需要更新的任务Id</param>
        /// <returns>返回Index界面</returns>
        public async Task<IActionResult> Update(int id)
        {
            //通过EF进行数据库操作
            //var temp = await _context.TodoItems.FindAsync(id);         
            //temp.IsComplete = true;
            //await _context.SaveChangesAsync();

            //通过ADO进行数据库操作
            using (SqlConnection conn = new SqlConnection(source))
            {
                await conn.OpenAsync();

                SqlCommand command = new SqlCommand("ItemComplete", conn);    //通过SqlCommand执行存储过程
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.AddWithValue("@Id", id);
                command.Parameters.AddWithValue("@IsComplete", 1);
                command.UpdatedRowSource = UpdateRowSource.None;
                await command.ExecuteNonQueryAsync();

                await conn.CloseAsync();
            }


            return RedirectToAction(nameof(Index));
        }
    }
}
